import pymysql
import logging

logger = logging.getLogger('log')
from ..models import ptTestDatabase
from django.http import JsonResponse


def operate_DbSql(id, sql):
    '''
    用来WEB SQL查询
    '''

    try:
        database = ptTestDatabase.objects.filter(id=id)
        host = database.values("host")[0]["host"]
        username = database.values("username")[0]["username"]
        password = database.values("password")[0]["password"]
        port = database.values("port")[0]["port"]
        connect = pymysql.Connect(
            host=str(host), user=str(username), passwd=str(password), port=int(port), database='apipt',charset='utf8'
        )
        cursor1 = connect.cursor()
        cursor1.execute(sql)
        rawData = cursor1.fetchall()
        col_names = [desc[0] for desc in cursor1.description]  # 获取所有列名
        result = []
        for row in rawData:
            objDict = {}
            # 把每一行的数据遍历出来放到Dict中  enumerate 函数用于将一个可遍历的数据对象(如列表、元组或字符串)组合为一个索引序列，同时列出数据和数据下标
            for index, value in enumerate(row):  # row={A,B} =》 0 A / 1 B connection
                objDict[col_names[index]] = value
                print(value)
            result.append(objDict)
        cursor1.close()
        connect.close()

        return result

    except  Exception as e:
        # 捕捉异常 打印内容
        logger.error(e)
    finally:
        pass

# if __name__ == '__main__':
#
#     pass
